 /*
	SELECT PROCESS_STEP_ID FROM FN_GET_BOM_PROCESS_STEP_ID('AT93C66A-W1827','ASSEMBLY' )
*/
CREATE  FUNCTION FN_GET_BOM_PROCESS_STEP_ID( @PART_NUMBER VARCHAR(50), @PS_TYPE_NAME VARCHAR(50) )
  
  RETURNS  @FN_RESULT TABLE( PROCESS_STEP_ID INT )
 
 AS
 BEGIN	
 	DECLARE @TEMP_PROCESSSTEP_ID INT
 	
 	--CHECK WEATHER PROCESSSTEP_ID IS EXIST IN PROCESS_STEP TABLE, IF NOT FOUND BRING IT FROM PRODUCT_MAP_TABLE TABLE
 	SELECT @TEMP_PROCESSSTEP_ID = PS.PROCESSSTEP_ID FROM PRODUCT P, PROCESS_STEP PS WHERE  P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PART_NUMBER = @PART_NUMBER 
	 				AND PS_TYPE_NAME = @PS_TYPE_NAME AND P.P_TYPE = 'BOM'
	
 	IF @TEMP_PROCESSSTEP_ID IS NULL
 	BEGIN
 		SELECT @TEMP_PROCESSSTEP_ID = PS.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE PMT, PROCESS_STEP PS, PRODUCT P
 			WHERE PMT.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
			AND PMT.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = @PS_TYPE_NAME AND P.P_TYPE = 'BOM'
 	END

 	IF @TEMP_PROCESSSTEP_ID > 0
 	BEGIN
 		INSERT INTO @FN_RESULT VALUES (@TEMP_PROCESSSTEP_ID) 
 	END
 RETURN
 END